<?php
/*! \brief
 * This is abstract class of WFSql class
 * This class main purpose is to generate SQL statement for various type database
 * This class always cooperate with class WFParameter
 * EX:
 * $objParameter = WF::getParameter();
 * $objParameter->appendSelect('widget_id');
 * $objParameter->appendFrom('WIDGET');
 * $objParameter->andWhereEqual('widget_id',1);
 * $objParameter->orderByDesc('widget_id');
 * 
 * $data = WF::getWidgetPoolFactory()->getDataObject()->querySelectAllByParameter($objParameter);
 * $data = WF::getWidgetPoolFactory()->getDataObject()->querySelectRowByParameter($objParameter);
 * $data = WF::getWidgetPoolFactory()->getDataObject()->querySelectCountByParameter($objParameter);
 * $data = WF::getWidgetPoolFactory()->getDataObject()->querySelectUpdateByParameter($objParameter);
 * $data = WF::getWidgetPoolFactory()->getDataObject()->querySelectDeleteByParameter($objParameter);
 * \author William Su
 */
abstract class ASql
{
	/**
	 * @var WF
	 */
	protected $objFramework;
    protected static $instance;
	protected $tmp;
	public function __construct($objFramework){
		$this->objFramework = $objFramework;
	}
	/**
	 *\brief Description
     * Get Insert Sql statement by $objParameter
     * @param WFParameter $objParameter
     */
	public function getSqlInsertByParameter($objParameter){
        $assocData = $objParameter->getAssocData();
	    if(!(is_array($assocData) && $assocData)){
        	throw new WFDbSqlException(__CLASS__ . "::" . __FUNCTION__ . "() INSERT statement required \$assocData!" );
        }
	    if(null === $objParameter->getFrom()){
            throw new WFDbSqlException(__CLASS__.'::'.__FUNCTION__.'()=>DELETE SQL Statement error!$objParameter->getFrom() is empty!');
        }
        $ret = "INSERT INTO ";
        $ret .= $objParameter->getFrom();
        $arrColName = array();
        $arrColValue = array();
        $arrValue = array();
        foreach($assocData as $key => $value){
        	$arrColName[] = $key;
        	$arrColValue[] = ':'.$key;
        	$this->tmp['getAssocDataPDO'][':'.$key] = $value;
        }
        $ret .= '('.implode(',',$arrColName).')';
        $ret .= ' VALUES';
        $ret .= "(".implode(",",$arrColValue).")";
        return $ret;
    }
    /**
	 *\brief Description
     * Get assocData format for PDO after call $objSql->getSqlXxxByParameter($objParameter);
     * @param WFParameter $objParameter
     * @return array assocData format for PDO
     */
    public function getAssocDataPDO(){
    	$ret = $this->tmp['getAssocDataPDO'];
    	return $ret;
    }
    /**
     *\brief Description
     * Get Update Sql statement by $objParameter
     * @param WFParameter $objParameter
     */
	public function getSqlUpdateByParameter($objParameter){
        $assocData = $objParameter->getAssocData();
        if(!(is_array($assocData) && $assocData)){
            throw new WFDbSqlException(__CLASS__.'::'.__FUNCTION__.'()=>UPDATE SQL Statement $assocData is required!');
        }
	    if(null === $objParameter->getFrom()){
            throw new WFDbSqlException(__CLASS__.'::'.__FUNCTION__.'()=>DELETE SQL Statement error!$objParameter->getFrom() is empty!');
        }
        $ret = "UPDATE ";
        $ret .= $objParameter->getFrom();
        $arrColNameValue = array();
        $arrValue = array();
        foreach($assocData as $key => $value){
        	$arrColNameValue[] = $key.'='.':'.$key;
        	$this->tmp['getAssocDataPDO'][':'.$key] = $value;
        }
        $ret .= ' SET ';
        $ret .= implode(',',$arrColNameValue);
        if(null !== $objParameter->getWherePDO()){
            $ret .= ' WHERE '.$objParameter->getWherePDO();
        }
	    $arrAssocDataPDOWhere = $objParameter->getAssocDataPDO();
        if(is_array($arrAssocDataPDOWhere) && $arrAssocDataPDOWhere){
        	foreach($arrAssocDataPDOWhere as $key => $value){
        		$this->tmp['getAssocDataPDO'][$key] = $value;
        	}
        }
        return $ret;
    }
    /**
     *\brief Description
     * Get Delete Sql statement by $objParameter
     * @param WFParameter $objParameter
     */
    public function getSqlDeleteByParameter($objParameter){
        $ret = "DELETE FROM ";
        if(null === $objParameter->getFrom()){
            throw new WFDbSqlException(__CLASS__.'::'.__FUNCTION__.'()=>DELETE SQL Statement error!$objParameter->getFrom() is empty!');
        }
        $ret .= $objParameter->getFrom();
        if(null !== $objParameter->getWherePDO()){
            $ret .= ' WHERE '.$objParameter->getWherePDO();
        }
        $arrAssocDataPDOWhere = $objParameter->getAssocDataPDO();
        if(is_array($arrAssocDataPDOWhere) && $arrAssocDataPDOWhere){
        	foreach($arrAssocDataPDOWhere as $key => $value){
        		$this->tmp['getAssocDataPDO'][$key] = $value;
        	}
        }
        return $ret;
    }
    /**
     *\brief Description
     * Get Select Select Sql COUNT statement by $objParameter
     * @param WFParameter $objParameter
     */
    public function getSqlSelectCountByParameter($objParameter){
        $ret = "SELECT ";
        $ret .= 'COUNT(1) AS GroupCount';
        $ret .= $this->_getSqlSelectFromByParameter($objParameter);
        return $ret;
    }
    /**
     *\brief Description
     * @param WFParameter $objParameter
     */
    public function getSqlSelectByParameter($objParameter){
        $ret = "SELECT ";
        $ret .= $objParameter->getSelect();
        $ret .= $this->_getSqlSelectFromByParameter($objParameter);
        return $ret;
    }
    /**
     *\brief Description
     * Get Sql statement by $objParameter
     * @param WFParameter $objParameter
     */
    protected function _getSqlSelectFromByParameter($objParameter){
        if(null === $objParameter->getFrom()){
            throw new WFDbSqlException(__CLASS__.'::'.__FUNCTION__.'()=>DELETE SQL Statement error!$objParameter->getFrom() is empty!');
        }
    	$ret = " FROM ".$objParameter->getFrom();
        if(null !== $objParameter->getWherePDO()){
        	$ret .= ' WHERE '.$objParameter->getWherePDO();
        	$assocDataPDO = $objParameter->getAssocDataPDO();
        	if(is_array($assocDataPDO) && $assocDataPDO){
        		foreach($assocDataPDO as $key => $value){
        			$this->tmp['getAssocDataPDO'][$key] = $value;
        		}
        	}
        }
        $ret .= (null !== $objParameter->getGroupBy()) ? ' GROUP BY '.$objParameter->getGroupBy() : '';
        $ret .= (null !== $objParameter->getHaving()) ? ' HAVING '.$objParameter->getHaving() : '';
        $ret .= (null !== $objParameter->getOrderBy()) ? ' ORDER BY '.$objParameter->getOrderBy() : '';
        $ret .= (null !== $objParameter->getLimit()) ? ' LIMIT '.$objParameter->getLimitStart().','.$objParameter->getLimitEnd() : '';
        return $ret;
    }
}